package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.Zone;

/**
 * A class used for managing zone
 */
public class ZoneDAO {

	/** a database of this zone */
    private Database db;
    
    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public ZoneDAO(Database db) {
        this.db = db;
    }
    
    /**
     * a method for searching all zone in database
     * @return all zone
     */
    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM zone ORDER BY zone_id ASC";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    /**
     * a method for searching the zone by site in database
     * @return zones where are in the site
     */
    public List<Map<String, Object>> findZoneBySite(int pnSiteId) {
        String vaSql = "SELECT zone_id, zone_name FROM zone WHERE site_id = ? ORDER BY zone_id ASC";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    //get Zone_name
    public String findZoneNameBySiteAndZoneId(int pnSiteId, int pnZoneId) {
        String vaSql = "SELECT ZONE_NAME FROM zone WHERE site_id = ? and zone_id = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnZoneId);
        if (result != null) {
            return (String) result.get("ZONE_NAME");
        } else {
            return null;
        }
    }
    
    //get ZoneID for check duplicate for insert data
    public String findZoneIdBySiteAndZoneId(int pnSiteId, int pnZoneId) {
        String vaSql = "SELECT zone_id FROM zone WHERE site_id = ? and zone_id = ? ";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnZoneId);
        if (result != null) {
            return (String) result.get("ZONE_ID").toString();
        } else {
            return null;
        }
    }
    
    //get ZoneID for check duplicate for update data
    public String findsZoneIdBySiteAndZoneId(int pnSiteId, int pnOldZoneId,int pnNewZoneID) {
        String vaSql = "SELECT zone_id FROM zone WHERE site_id = ? and zone_id != ? and zone_id = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId,pnOldZoneId,pnNewZoneID);
        if (result != null) {
            return (String) result.get("ZONE_ID").toString();
        } else {
            return null;
        }
    }
   
    
    /**
     * a method for adding a zone into database
     * @param poZone ZONE to add
     */
    public void addZone(Zone poZone) {
    	 String vaSql = "INSERT INTO ZONE (SITE_ID,ZONE_ID,ZONE_NAME,INSERT_USER_ID,INSERT_DATE,INSERT_TIME,"
    	 								+ "UPDATE_USER_ID,UPDATE_DATE,UPDATE_TIME)"
    			 						+"VALUES(?,?,?,?,?,?,?,?,?)";
    			try {
    					db.insert(vaSql, poZone.getVnSiteId(),
    							poZone.getVnZoneId(),
    							poZone.getVaZoneName(),
    							poZone.getVnInsertUserId(),
    							poZone.getVaInsertDate(),
    							poZone.getVaInsertTime(),
    							poZone.getVnUpdateUserId(),
    							poZone.getVaUpdateDate(),
    							poZone.getVaUpdateTime());
    				
    			}catch (Exception e) {
    	        	throw new RuntimeException(e);
    	        }
    	}
    
    /**
     * a method for counting zone by site_id
     * @param pnSiteId site that is wanted to count
     * @return total of counted
     */
    public int getAllCountZoneBySiteId(int pnSiteId) {
        String vaSql = "SELECT count(*) FROM ZONE WHERE site_id=?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId);

        if (result != null) {
            return Integer.parseInt(result.get("COUNT(*)").toString());
        } else {
            return 0;
        }
    }
    
    
    
    
    /**
     * delete zone by site_id, zone_id
     * @param pnSiteId is an site_id that want to delete data
     * @param pnZoneId is an pnZoneId that want to delete data
     * @return true if success, false if fail
     */
    public boolean deleteCustomerByAccount(int pnSiteId, int pnZoneId) {
        String vaSql = "DELETE FROM ZONE WHERE SITE_ID = ? and ZONE_ID = ?";
        try {
            db.update(vaSql, pnSiteId, pnZoneId);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
    
    
    /**
     * a method for updating a zone into database
     * @param pnSiteId site that zone live
     * @param pnZoneId that will update
     * @param poNewZone that will update
     */
    public void updateZone(int pnSiteId, int pnZoneId,Zone poNewZone) {
    	 //String vaSql = "UPDATE ZONE SET ZONE_NAME = ? ,UPDATE_USER_ID=?,UPDATE_DATE=?,UPDATE_TIME=?"
    	 	// 	+ "WHERE SITE_ID=? and ZONE_ID=?";
    	 	 
    	String vaSql  = " UPDATE ZONE SET " +
    				  " ZONE_ID = ? , " +
    				  " ZONE_NAME  = ? , " +
    				  " UPDATE_USER_ID = ? , "+
    				  " UPDATE_DATE = ? , " +
    				  " UPDATE_TIME = ? " +
    				  " WHERE " + 
    				  " ZONE_ID  = ? " +
    				  " AND SITE_ID = ? ";
    	
    	 try {
				db.update(vaSql, poNewZone.getVnZoneId() , 
						poNewZone.getVaZoneName(),
						poNewZone.getVnUpdateUserId(),
						poNewZone.getVaUpdateDate(),
						poNewZone.getVaUpdateTime(),
						pnZoneId,
						pnSiteId);
							
		}catch (Exception e) {
			throw new RuntimeException(e);
		}
    }
    /**
     * this update zone data 
     * @return 
     */
    public int update(){ 
    	return 1;
    }
    
    
	
}
